
USE master
GO

-- The following code is basically tied to rights issues that I decided to look 
-- into after reading this article: 
-- http://www.sqlservercentral.com/articles/Administration/64974/
-- While it doesn't so much cover rights it got me thinking.  The code here is 
-- directly to resolve permissions issues that could cause logons to be denied
-- because the user doesn't have sufficient rights to log their logon.  This was
-- tested with a user that had ONLY a sql logon to the server and no other 
-- rights.  That user was unable to logon.  I have added a login called
-- AuditCredLogin and a user AuditCredUser, this user exists in both the Audit
-- and Repository databases.  This login has a long nasty password assembled 
-- from 2 GUIDs that are generated locally, the password is not displayed and 
-- does not need to be know, it is NEVER used to actually login to the server, 
-- therefore is disabled.

DECLARE @pwd VARCHAR(80)

-- Generate random LONG password, we don't need to know what it is..
SELECT @pwd = CONVERT( VARCHAR(38), NEWID() ) + ' - ' + CONVERT( VARCHAR(38), NEWID() )

IF NOT EXISTS ( SELECT 'X' FROM sys.server_principals WHERE name = 'AuditCredLogin' )
BEGIN -- Login doesn't already exist so we need to create it.
     DECLARE @cmd varchar(8000)

     SELECT @cmd = 'CREATE LOGIN AuditCredLogin WITH PASSWORD = '''
                 + @pwd
                 + ''', '
                 + 'DEFAULT_DATABASE = [tempdb], CHECK_EXPIRATION = OFF, CHECK_POLICY = OFF '
     EXEC ( @cmd )
END
GO

IF NOT EXISTS ( SELECT 'X' FROM sys.database_principals WHERE name = 'AuditCredUser' )
BEGIN -- User doesn't already exist so we need to create it.
     DECLARE @cmd varchar(8000)

     SELECT @cmd = 'CREATE USER AuditCredUser FROM LOGIN AuditCredLogin; '
     EXEC ( @cmd )
END
GO

-- Permission needed for Logon Trigger.
DECLARE @cmd varchar(8000)

SELECT @cmd = 'GRANT VIEW SERVER STATE TO AuditCredLogin;'
EXEC ( @cmd )
GO

-- Disable the AuditCredLogin login, we don't need to actually use it 
ALTER LOGIN AuditCredLogin DISABLE
GO
IF  EXISTS ( SELECT * 
             FROM   master.sys.server_triggers 
             WHERE  parent_class_desc = 'SERVER' 
             AND    name = N'LogAllDDL' )
BEGIN
     IF EXISTS ( SELECT 'X' 
                 FROM   sys.server_triggers 
                 WHERE  parent_class_desc = 'SERVER' 
                 AND    name              = 'LogAllDDL' 
                 AND    is_disabled       = 0 )
     BEGIN
          EXEC( 'DISABLE TRIGGER [LogAllDDL] ON ALL SERVER' )
     END

     EXEC( 'DROP TRIGGER [LogAllDDL] ON ALL SERVER' )
END
GO

CREATE TRIGGER [LogAllDDL]
ON ALL SERVER 
WITH EXECUTE AS 'AuditCredLogin', ENCRYPTION 
FOR DDL_EVENTS
AS
BEGIN
     SET NOCOUNT ON
     SET ANSI_PADDING ON

     DECLARE @data xml
     SET @data = EVENTDATA()

     EXEC DBAObjects.dbo.InsDDLActivity @EventData = @data
END
GO

--Enable the trigger.
IF EXISTS ( SELECT 'X' 
            FROM   sys.server_triggers 
            WHERE  parent_class_desc = 'SERVER' 
            AND    name              = 'LogAllDDL' 
            AND    is_disabled       = 1 )
BEGIN
     EXEC ( 'ENABLE TRIGGER [LogAllDDL] ON ALL SERVER' )
END
GO
IF  EXISTS ( SELECT * 
             FROM   master.sys.server_triggers 
             WHERE  parent_class_desc = 'SERVER' 
             AND    name = N'LogAllLogons' )
BEGIN
     IF EXISTS ( SELECT 'X' 
                 FROM   sys.server_triggers 
                 WHERE  parent_class_desc = 'SERVER' 
                 AND    name              = 'LogAllLogons' 
                 AND    is_disabled       = 0 )
     BEGIN
          EXEC( 'DISABLE TRIGGER [LogAllLogons] ON ALL SERVER' )
     END

     EXEC( 'DROP TRIGGER [LogAllLogons] ON ALL SERVER' )
END
GO

CREATE TRIGGER [LogAllLogons] 
ON ALL SERVER 
WITH EXECUTE AS 'AuditCredLogin', ENCRYPTION
FOR LOGON
AS
BEGIN
     SET NOCOUNT ON

     DECLARE @data xml
     SET @data = EVENTDATA()

     EXEC DBAObjects.dbo.InsLogonActivity @EventData = @data
END
GO

-- Enable the trigger.
IF EXISTS ( SELECT 'X' 
            FROM   sys.server_triggers 
            WHERE  parent_class_desc = 'SERVER' 
            AND    name              = 'LogAllLogons' 
            AND    is_disabled       = 1 )
BEGIN
     EXEC ( 'ENABLE TRIGGER [LogAllLogons] ON ALL SERVER' )
END
GO
